<?php

require 'db_config.php';

class Service {
	
	private $db_connection;
	
	function __construct() {
		date_default_timezone_set('Europe/Warsaw');
		// Nawiazanie polaczenia z baza danych:
		$this->db_connection = mysql_connect(Config::$db_host, Config::$db_user, Config::$db_password) or die ('Error: Connection to database failed.');
		mysql_set_charset("utf8");
		if ($this->db_connection) {
			// Wybor bazy danych:
			mysql_select_db(Config::$db_name, $this->db_connection) or die('Error: Selecting database failed');			
		}
	}
	
	function getProductData() {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			$queryResult = mysql_query("SELECT p1.*, ifnull(p3.price,p2.price) as price FROM `products` p1 JOIN `prices` p2 ON p1.id = p2.product_id AND p2.company_id IS NULL LEFT JOIN `prices` p3 ON p1.id = p3.product_id AND p3.company_id = (SELECT `company_id` FROM `company_session` WHERE `session_id` = \"".session_id()."\")");
			$rows = array();
			while($r = mysql_fetch_assoc($queryResult)) {
				$rows[] = $r;
			}
			$result["result"] = $rows;
		}
		return $result;
	}
	
	function getDepartments() {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			$queryResult = mysql_query("SELECT * FROM `departments` WHERE company_id = (SELECT `company_id` FROM `company_session` WHERE `session_id` = \"".session_id()."\")");
			$rows = array();
			while($r = mysql_fetch_assoc($queryResult)) {
				$rows[] = $r;
			}
			$result["result"] = $rows;
		}
		return $result;
	}
	
	function saveOrder($orderArr) {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			if ($orderArr["mDbId"] != 0) {
				$orderId = $orderArr["mDbId"];
				$result["orderId"] = $orderId;
				mysql_query("UPDATE `orders` SET `number` = \"".$orderArr["mNumber"]."\", `costUnit` = \"".$orderArr["mCostUnit"]."\", `date` = \"".$orderArr["mDate"]."\", `state` = \"".$orderArr["mState"]."\", `paymentMethod` = \"".$orderArr["mPaymentMethod"]."\" WHERE `id` = \"".$orderId."\"");
				
				$queryResult = mysql_query("SELECT `id`, `department_id`, `product_id` FROM `order_elements` WHERE `order_id` = \"".$orderId."\"");
				$doneIds = array();
				$removedDepIds = array();
				while($r = mysql_fetch_assoc($queryResult)) {
					if (isset($removedDepIds[$r["department_id"]])) // departament został usunięty
						continue;
					if (!isset($orderArr["mSubOrders"][$r["department_id"]])) { // departament jest w tabeli, ale nie ma go w danych od klienta
						mysql_query("DELETE FROM `order_elements` WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$r["department_id"]."\"");
						$removedDepIds[] = $r["department_id"];
					} else { // departament jest w tabeli i jest w danych od klienta
						$v = $orderArr["mSubOrders"][$r["department_id"]];
						$updated = false;
						foreach ($v as $key => $val) {
							if ($val["mProductId"] == $r["product_id"]) {
								mysql_query("UPDATE `order_elements` SET `price` = \"".$val["mPrice"]."\", `quantity` = \"".$val["mQuantity"]."\", `reinvoice` = \"".$val["mReinvoice"]."\" WHERE `id` = \"".$r["id"]."\"");
								unset($orderArr["mSubOrders"][$r["department_id"]][$key]);
								$updated = true;
								break;
							}
						}
						if (!$updated) // produkt jest w tabeli, ale nie ma go w danych od użytkownika
							mysql_query("DELETE FROM `order_elements` WHERE `id` = \"".$r["id"]."\"");
					}
				}
				foreach ($orderArr["mSubOrders"] as $k => $v) {
					foreach ($v as $v2) {
						mysql_query("INSERT INTO `order_elements` (`order_id`, `department_id`, `product_id`, `price`, `quantity`, `reinvoice`) VALUES (\"".$orderId."\", \"".$k."\", \"".$v2["mProductId"]."\", \"".$v2["mPrice"]."\", \"".$v2["mQuantity"]."\", \"".$v2["mReinvoice"]."\")");
					}
				}
			} else {
				mysql_query("INSERT INTO `orders` (`number`, `costUnit`, `date`, `state`, `paymentMethod`, `user_id`) VALUES (\"".$orderArr["mNumber"]."\", \"".$orderArr["mCostUnit"]."\", \"".$orderArr["mDate"]."\", \"".$orderArr["mState"]."\", \"".$orderArr["mPaymentMethod"]."\", (SELECT `user_id` FROM `sessions` WHERE `session_id` = \"".session_id()."\"))");
				$orderId = mysql_insert_id();
				$result["orderId"] = $orderId;
				foreach ($orderArr["mSubOrders"] as $k => $v) {
					foreach ($v as $v2) {
						mysql_query("INSERT INTO `order_elements` (`order_id`, `department_id`, `product_id`, `price`, `quantity`, `reinvoice`) VALUES (\"".$orderId."\", \"".$k."\", \"".$v2["mProductId"]."\", \"".$v2["mPrice"]."\", \"".$v2["mQuantity"]."\", \"".$v2["mReinvoice"]."\")");
					}
				}
			}
		}
		return $result;
	}
	
	function getOrderList() {
		$result = array("exitCode" => 0, "result" => "");
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			$queryResult = mysql_query("SELECT * FROM `orders` WHERE `user_id` = (SELECT `user_id` FROM `sessions` WHERE `session_id` = \"".session_id()."\")");
			$rows = array();
			while($r = mysql_fetch_assoc($queryResult)) {
				$queryResult2 = mysql_query("SELECT DISTINCT `department_id` FROM `order_elements` WHERE `order_id` = \"".$r["id"]."\"");
				while ($r2 = mysql_fetch_assoc($queryResult2)) {
					$r["subOrders"][] = array($r2["department_id"] => array());
				}
				$rows[] = $r;
			}
			$result["result"] = $rows;
		}
		return $result;
	}
	
	function getOrder($orderId) {
		$result = array("exitCode" => 0, "result" => "");
		if ($this->checkCredentials()) {
			$queryResult = mysql_query("SELECT `number`, `costUnit`, `date`, `state`, `paymentMethod` FROM `orders` WHERE `id` = \"".$orderId."\"");
			if (mysql_affected_rows() == 0) {
				return $result;
			}
			$result["exitCode"] = 1;
			$rows = array();
			while($r = mysql_fetch_assoc($queryResult)) {
				$queryResult2 = mysql_query("SELECT * FROM `order_elements` WHERE `order_id` = \"".$orderId."\"");
				while ($r2 = mysql_fetch_assoc($queryResult2)) {
					$r["subOrders"][] = array($r2["department_id"] => array(
						"product_id" => $r2["product_id"],
						"price" => $r2["price"],
						"quantity" => $r2["quantity"],
						"reinvoice" => $r2["reinvoice"]));
				}
				$rows[] = $r;
			}
			$result["result"] = $rows;
		}
		return $result;
	}
	
	function login($username, $password) {
		$result = array("exitCode" => 0,
						"message" => "",
						"name" => "",
						"address1" => "",
						"address2" => "",
						"contact" => "",
						"image" => 0);
		if ($this->db_connection) {
			// Przefiltrowanie parametrow:
			$username = mysql_real_escape_string($username, $this->db_connection);
			$password = mysql_real_escape_string($password, $this->db_connection);
			
			// Sprawdzenie poprawności nazwy użytkownika i hasła
			$queryResult = mysql_query("SELECT u.`id`, u.`password`, u.`salt`, c.`name`, c.`address1`, c.`address2`, c.`contact`, c.`id` as company_id FROM `users` u JOIN `companies` c ON c.`id` = u.`company_id` WHERE u.`login` = '".$username."'");
			if (mysql_affected_rows() != 0) // Użytkownik istnieje
			{
				$row = mysql_fetch_assoc($queryResult);
				if ($row['password'] == md5($password.$row['salt'])) {
					$result["exitCode"] = 1;
					$result["message"] = "OK";
					$result["name"] = $row["name"];
					$result["address1"] = $row["address1"];
					$result["address2"] = $row["address2"];
					$result["contact"] = $row["contact"];
					$result["image"] = $row["company_id"];
					$sid = session_id();
					$queryResult = mysql_query("SELECT * FROM `sessions` WHERE `session_id` = \"".$sid."\" AND `user_id` = \"".$row["id"]."\"");
					if (mysql_affected_rows() == 0) {
						mysql_query("DELETE FROM `sessions` WHERE `user_id` = \"".$row["id"]."\"");
						$queryResult = mysql_query("INSERT INTO `sessions` (`session_id`, `user_id`, `ip_address`) VALUES (\"".$sid."\", \"".$row["id"]."\", \"".$_SERVER['REMOTE_ADDR']."\")");
						if (!$queryResult) {
							$result["exitCode"] = 0;
							$result["message"] = mysql_error();
						}
					}
				} else
					$result["message"] = "Hasło niepoprawne.";
			} else
				$result["message"] = "Użytkownik nie istnieje.";
		}
		return $result;
	}
	
	function register($username, $password, $name, $address) {
		$result = array("exitCode" => 0);
		if ($this->db_connection) {
			
			// Przefiltrowanie parametrow:
			$username = mysql_real_escape_string($username, $this->db_connection);
			$password = mysql_real_escape_string($password, $this->db_connection);
			$name = mysql_real_escape_string($name, $this->db_connection);
			$address = mysql_real_escape_string($address, $this->db_connection);
				
			// Wygenerowanie hashu hasła
			$salt = sha1(md5($password));
			$passHashed = md5($password.$salt);
			try 
			{
				$queryResult = mysql_query("INSERT INTO `users` (`login`, `password`, `salt`, `name`, `address`) values (\"".$username."\", \"".$passHashed."\", \"".$salt."\", \"".$name."\", \"".$address."\");");
				if (!$queryResult) throw new Exception("Error 0: " . mysql_error());
				if (mysql_affected_rows() != 0) // Konto zostalo utworzone
					$result["exitCode"] = 1;
			} 
			catch (Exception $e) 
			{
				$result["exitCode"] = 2;
				$result["message"] = $e->getMessage();
			}
		}
		return $result;
	}
	
	function checkCredentials() {
		if ($this->db_connection) {
			mysql_query("SELECT * FROM `sessions` WHERE `session_id` = \"".session_id()."\" AND `ip_address` = \"".$_SERVER['REMOTE_ADDR']."\"");
			return (mysql_affected_rows() != 0);
		}
		return false;
	}
	
	function __destruct() {
		mysql_close($this->db_connection);
	}
}

?>